package me.baicai.modules.belldandy.gcb.repository;

import com.alibaba.fastjson.JSONObject;
import me.baicai.modules.belldandy.gcb.domain.Project;
import me.baicai.modules.belldandy.gcb.domain.UserDividend;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;


/**
* @author 超级大白菜
* @date 2020-06-02
*/
public interface UserDividendRepository extends JpaRepository<UserDividend, Integer>, JpaSpecificationExecutor<UserDividend> {

    @Transactional
    void deleteAllByPid(Integer id);

    @Query(value = "select p.*,u.commission from user_dividend u LEFT JOIN project p on p.id = u.pid where u.uid = ?1", nativeQuery = true)
    List<JSONObject> getUserDividend(Integer uid);

    @Query(value = "select a.amount - b.amount from (select sum(amount) as amount from finance where type = 'out') as a , (select sum(amount) as amount from finance where type = 'in') as b", nativeQuery = true)
    Float 资金池();

    @Query(value = "select u.commission,c.* from user_dividend u RIGHT JOIN  crm_order c on c.projectid = u.pid and DATE_FORMAT( c.created, '%Y-%m' ) = ?3 where u.uid = ?1 and u.pid = ?2", nativeQuery = true)
    List<JSONObject> projectInfo(Integer uid, Integer pid, String date);

    @Query(value = "select ifnull(sum(amount),0) as amount from finance f where f.type = ?1 and DATE_FORMAT( f.createDate, '%Y-%m' ) = ?2", nativeQuery = true)
    Float 月度求和(String out, String date);

    @Query(value = "select ifnull(sum(amount),0) as amount from finance f where f.type = ?3 and DATE_FORMAT( f.createDate, '%Y-%m' ) = ?2 and souce = ?1", nativeQuery = true)
    Float 项目月度求和(String soure, String date, String in);

    @Query(value = "SELECT `value` FROM dict_detail where label = ?1", nativeQuery = true)
    String 获取字典(String 结算方式);

    @Query(value = "select * from user_dividend where uid = ?1 and pid = ?2", nativeQuery =true)
    UserDividend getUserDividend(Integer uid, Integer pid);
}
